package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.NetflowWRData;
import com.bj58.data.emailreports.service.NetflowMonthReportService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.DateUtil;

public class NetflowMonthReportServiceImpl implements NetflowMonthReportService {
	
	private CommonDataDeal comDeal = new CommonDataDeal();

	
	@Override
	public Map<String,List<NetflowWRData>> getNMRPlatformDataMap(List<String> dateList,String platformType,String[] business_58,String[] business_ganji)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();			
		
		for(String searchDate : dateList){
					
			String yyyyMM_58 = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			String yyyy_ganji = searchDate.substring(0,searchDate.indexOf("-"));
			
			if(yyyy_ganji.equals("2015"))
			{
				yyyy_ganji="2014";
			}
			
			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
				
			//58
			for(String businessType : business_58){
			
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','58_"+businessType+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv  ");			
				if("app".equals(platformType)){
					sql.append(" from t_netflow_"+platformType+"_pv_"  + yyyyMM_58);
				}
				else{
					sql.append(" from t_netflow_"+platformType+"_source_cate_pv_"  +  yyyyMM_58);
				}					
		
				sql.append(" where business_type='"+businessType+ "'");
				
				sql.append(" and disp_cate1_name='all'");
				if("app".equals(platformType)){
					sql.append(" and  disp_city1_name='all' ");
				}
				else{
					sql.append(" and source1='all'");
				}		
				sql.append(" group by stat_date ");
	
				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_app.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("pv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
			}
			
			//ganji
			for(String businessType : business_ganji){	

				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','ganji_"+businessType+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,business_type,sum(case when page_type='详情页' then pv else 0  end) vppv,sum(case when page_type='-' then uv else 0  end) uv ," +
						"sum(case when page_type='-' then pv else 0  end) pv  ");			
				
				sql.append(" from t_netflow_ganji_" + yyyy_ganji);
									
				if("all".equals(businessType))
				{
					sql.append(" where business_type ='-' ");
				}
				else
				{
					sql.append(" where business_type='"+businessType+"' ");
				}
				
				sql.append(" and stat_date>='"+beginday+"' and stat_date<='"+endday+"'");
				
				sql.append(" and cate2='-' and source1='-' ");
				if("pc".equals(platformType)){
					sql.append(" and  platform='web' ");
				}else if("m".equals(platformType)){
					sql.append(" and platform='wap'");
				}else
				{
					sql.append(" and platform='app'");
				}
				sql.append(" group by stat_date ");	
				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_tmp.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("pv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
		   
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//把all相关放入总体map
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();
		
		for (NetflowWRData vo : rsList)
		{
			if(vo.getType().contains("all"))
			{
				allCountMap.put(vo.getStatDate()+vo.getType(), vo);	
			}
		}
	
		//计算占比
		for (NetflowWRData vo : rsList){
			
			if(vo.getType().contains("all"))
				continue;
		
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setPvZb((double) vo.getPv() / allVo.getPv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}
		
		return rsMap;
	}
	
	@Override
	public Map<String,List<NetflowWRData>> getNMRALLDataMap(List<String> dateList,Map<String,List<NetflowWRData>> NMRPCBusiDataMap, Map<String,List<NetflowWRData>> NMRMBusiDataMap,Map<String,List<NetflowWRData>> NMRAPPBusiDataMap)
			throws Exception {
		
		String[] cate={"58","ganji"};
		String[] business={"all","fangchan","zhaopin","huangye","ershouche","ershou","ershouchuangxin","ershoutuiguang"};
		
		List<NetflowWRData> rsList=new ArrayList<NetflowWRData>();

		for(String c: cate){
		
			List<NetflowWRData> pcData=NMRPCBusiDataMap.get(c);
			List<NetflowWRData> mData=NMRMBusiDataMap.get(c);
			List<NetflowWRData> appData=NMRAPPBusiDataMap.get(c);
			for(String busi: business){
				
				if(c.equals("58") && (busi.equals("ershou") ))
					continue;
				if(c.equals("ganji") && (busi.equals("ershouchuangxin") || busi.equals("ershoutuiguang")))
					continue;
				
				for(String date:dateList)
				{
					NetflowWRData allvo = new NetflowWRData();
					allvo.setStatDate(date);
					allvo.setType(c+"_"+busi);
					
					for (NetflowWRData vo : pcData)
					{
						if(vo.getType().contains(busi) && vo.getStatDate().equals(date))
						{
							allvo.setVppv(vo.getVppv()+allvo.getVppv());
							allvo.setUv(vo.getUv()+allvo.getUv());		
							allvo.setPv(vo.getPv()+allvo.getPv());	
							break;
						}			
					}
					
					for (NetflowWRData vo : mData)
					{
						if(vo.getType().contains(busi) && vo.getStatDate().equals(date))
						{
							allvo.setVppv(vo.getVppv()+allvo.getVppv());
							allvo.setUv(vo.getUv()+allvo.getUv());		
							allvo.setPv(vo.getPv()+allvo.getPv());	
							break;
						}			
					}
					
					for (NetflowWRData vo : appData)
					{
						if(vo.getType().contains(busi) && vo.getStatDate().equals(date))
						{
							allvo.setVppv(vo.getVppv()+allvo.getVppv());
							allvo.setUv(vo.getUv()+allvo.getUv());		
							allvo.setPv(vo.getPv()+allvo.getPv());	
							break;
						}			
					}
	
					allvo.setVppv_uv((double)allvo.getVppv()/allvo.getUv());				
					rsList.add(allvo);
				}
			}	
		}

		
		//把all相关放入总体map
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();
		
		for (NetflowWRData vo : rsList)
		{
			if(vo.getType().contains("all"))
			{
				allCountMap.put(vo.getStatDate()+vo.getType(), vo);	
			}
		}
	
		//计算占比
		for (NetflowWRData vo : rsList){
			
			if(vo.getType().contains("all"))
				continue;
		
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setPvZb((double) vo.getPv() / allVo.getPv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}
		
		return rsMap;
	}

	
	@Override
	public Map<String,List<NetflowWRData>> getNMRSourceDataMap(List<String> dateList,Map<String,String> sourceMap_58,Map<String,String> sourceMap_ganji,String platform,String business)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		for(String searchDate : dateList){
			
			String yyyyMM_58 = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			String yyyy_ganji = searchDate.substring(0,searchDate.indexOf("-"));
			
			if(yyyy_ganji.equals("2015"))
			{
				yyyy_ganji="2014";
			}
			
			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）
							
			//58
			for(String source : sourceMap_58.keySet()){
			
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','58_"+source+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ");			
							
				if(platform.equals("pc"))
				{
					sql.append(" from t_netflow_pc_source_cate_pv_"  + yyyyMM_58);
				}
				else 
				{
					sql.append(" from t_netflow_m_source_cate_pv_"  + yyyyMM_58);
				}
																
				sql.append(" where business_type = '"+business+"'");
				sql.append(" and disp_cate1_name='all'  ");	
				if(platform.equals("pc"))
				{
					sql.append(" and source2='all' ");
				}
				
				sql.append(" and "+sourceMap_58.get(source)+ " ");			
				sql.append(" group by stat_date ");

				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_app.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
			}
			
			if(!business.equals("ershouchuangxin"))
			{
				//ganji
				for(String source : sourceMap_ganji.keySet()){	
							
					sql.delete(0, sql.length()+1);					
					sql.append(" select '" + searchDate + "','ganji_"+source+"',");
					sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
					sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
					sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
					sql.append(" from( ");
					
					sql.append(" select stat_date,business_type,sum(case when page_type='详情页' then pv else 0  end) vppv,sum(case when page_type='-' then uv else 0  end) uv ");			
					
					sql.append(" from t_netflow_ganji_" + yyyy_ganji);
										
					sql.append(" where stat_date >= '"+beginday+"'");
					sql.append(" and stat_date <= '"+endday+ "' ");	
					
					if(business.equals("all"))
					{
						sql.append(" and business_type='-' ");
					}else
					{
						sql.append(" and business_type= '"+business+"' ");
					}
					
					sql.append(" and source1 in ("+sourceMap_ganji.get(source)+ ") ");
	                
					if(platform.equals("pc")){
						sql.append(" and  platform='web' ");
					}else {
						sql.append(" and  platform='wap' ");
					}
									
					sql.append(" group by stat_date ");	
					sql.append(" )a ");	
					sql.append(" :"+ DBType.db_94_data_tmp.getValue());
											
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("type");
					cols1.add("vppv");
					cols1.add("uv");
					cols1.add("vppv_uv");

					sqlMap.put(sql.toString(), cols1);
			   
				}
			}
			
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//把总计放入map
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();

		for (NetflowWRData vo : rsList)
		{
			if(vo.getType().contains("all"))
			{
				allCountMap.put(vo.getStatDate()+vo.getType(), vo);
			}
		}
		
		//计算占比
		for (NetflowWRData vo : rsList){
			
			if(vo.getType().contains("all"))
				continue;
		
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}
		
		return rsMap;
	}
	
	@Override
	public Map<String,List<NetflowWRData>> getNMRBusiDataMap(List<String> dateList,String business)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		List<String> partformTypeList = new ArrayList<String>();
		partformTypeList.add("pc");
		partformTypeList.add("m");
		partformTypeList.add("app");
		
		for(String searchDate : dateList){
			
			String yyyyMM_58 = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
			String yyyy_ganji = searchDate.substring(0,searchDate.indexOf("-"));
			if(yyyy_ganji.equals("2015"))
			{
				yyyy_ganji="2014";
			}
			
			String beginday = DateUtil.firstDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月月第一天）
			String endday = DateUtil.lastDayMonthBefore(searchDate, "yyyy-MM-dd",0);//查询日期（本月最后一天）	
			
			//58
			for(String partformType : partformTypeList){
			
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','58_"+partformType+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv  ");			
				if("app".equals(partformType)){
					sql.append(" from t_netflow_"+partformType+"_pv_"  + yyyyMM_58);
				}
				else{
					sql.append(" from t_netflow_"+partformType+"_source_cate_pv_"  + yyyyMM_58);
				}					
					
				sql.append(" where business_type = '"+business+"'  and disp_cate1_name='all'");
				if("app".equals(partformType)){
					sql.append(" and  disp_city1_name='all' ");
				}
				else{
					sql.append(" and source1='all'");
				}
				sql.append(" group by stat_date ");		
				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_app.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("pv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
			}
			
			//ganji
			for(String partformType : partformTypeList){	
	
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','ganji_"+partformType+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,sum(case when page_type='详情页' then pv else 0  end) vppv,sum(case when page_type='-' then uv else 0  end) uv ," +
						"sum(case when page_type='-' then pv else 0  end) pv  ");			
				
				sql.append(" from t_netflow_ganji_" + yyyy_ganji);
									
				sql.append(" where stat_date >= '"+beginday+"'");
				sql.append(" and stat_date <= '"+endday+ "' ");	
				
				if(business.equals("all"))
				{
					sql.append(" and business_type='-' ");
				}else
				{
					sql.append(" and business_type='"+business+"' ");
				}
				sql.append(" and source1='-' ");
				if("pc".equals(partformType)){
					sql.append(" and  platform='web' ");
				}else if("m".equals(partformType)){
					sql.append(" and platform='wap'");
				}else
				{
					sql.append(" and platform='app'");
				}
				sql.append(" group by stat_date ");	
				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_tmp.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("pv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
		   
			}
	
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//计算三端总体
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();
		
		String[] cate={"58","ganji"};		
		
		for(String c:cate){
	
			for(String date:dateList)
			{			
				NetflowWRData allvo = new NetflowWRData();
				allvo.setStatDate(date);
				allvo.setType(c+"_all");
				for (NetflowWRData vo : rsList)
				{
					if(vo.getType().contains(c) && vo.getStatDate().equals(date))
					{
						allvo.setVppv(vo.getVppv()+allvo.getVppv());
						allvo.setUv(vo.getUv()+allvo.getUv());
						allvo.setPv(vo.getPv()+allvo.getPv());			
					}			
				}
				allvo.setVppv_uv((double)allvo.getVppv()/allvo.getUv());					
				allCountMap.put(allvo.getStatDate()+allvo.getType(), allvo);
				rsList.add(allvo);
				
			}
		}
		
		//计算占比
		for (NetflowWRData vo : rsList){
		
			if(vo.getType().contains("all"))
				continue;
			
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setPvZb((double) vo.getPv() / allVo.getPv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}
		
		return rsMap;
	}
	
	@Override
	public List<NetflowWRData> getNMRESCXDataList(List<String> dateList)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		List<String> partformTypeList = new ArrayList<String>();
		partformTypeList.add("pc");
		partformTypeList.add("m");
		partformTypeList.add("app");
		
		for(String searchDate : dateList){
			
			String yyyyMM_58 = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
	
			//58
			for(String partformType : partformTypeList){
			
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','58_"+partformType+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv  ");			
				if("app".equals(partformType)){
					sql.append(" from t_netflow_"+partformType+"_pv_"  + yyyyMM_58);
				}
				else{
					sql.append(" from t_netflow_"+partformType+"_source_cate_pv_"  + yyyyMM_58);
				}					
				
				//dateList 里是 本月月初（业务线）、 本月月末（cate）、上月（业务线）、去年当月（cate）
				if(searchDate.equals(dateList.get(0)) || searchDate.equals(dateList.get(2)))
				{
					sql.append(" where business_type = 'ershouchuangxin'  and disp_cate1_name='all'");
				}else
				{
					sql.append(" where ((business_type = 'all' and disp_cate1_name in ('二手市场','二手交易') and disp_cate2_name in ('办公用品/设备','艺术品/收藏品','手机号码','二手设备','通讯业务','成人用品','校园二手','二手求购')) " +
							"or (business_type = 'all' and disp_cate1_name = '宠物' and disp_cate2_name = 'all') " +
							"or (business_type = 'all' and disp_cate1_name in ('家政服务','回收') and disp_cate2_name = '二手回收'))");
				}
				
				if(!"m".equals(partformType)){
					sql.append(" and  disp_cate3_name='all' ");
				}
								
				if("app".equals(partformType)){
					sql.append(" and  disp_city1_name='all' ");
				}
				else{
					sql.append(" and source1='all'");
				}
				sql.append(" group by stat_date ");		
				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_app.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("pv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
			}
	
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//计算三端总体
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();
		
		for(String date:dateList)
		{			
			NetflowWRData allvo = new NetflowWRData();
			allvo.setStatDate(date);
			allvo.setType("58_all");
			for (NetflowWRData vo : rsList)
			{
				if(vo.getStatDate().equals(date))
				{
					allvo.setVppv(vo.getVppv()+allvo.getVppv());
					allvo.setUv(vo.getUv()+allvo.getUv());
					allvo.setPv(vo.getPv()+allvo.getPv());			
				}			
			}
			allvo.setVppv_uv((double)allvo.getVppv()/allvo.getUv());					
			allCountMap.put(allvo.getStatDate()+allvo.getType(), allvo);
			rsList.add(allvo);	
		}

		//计算占比
		for (NetflowWRData vo : rsList){
		
			if(vo.getType().contains("all"))
				continue;
			
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setPvZb((double) vo.getPv() / allVo.getPv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
	
		return rsList;
	}
	
	
	@Override
	public List<NetflowWRData>  getESCXSourceDataList(List<String> dateList,Map<String,String> sourceMap_58,String platform)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		//dateList 里是 本月月初（业务线）、 本月月末（cate）、上月（业务线）、去年当月（cate）
		for(String searchDate : dateList){
			
			String yyyyMM_58 = searchDate.substring(0,searchDate.lastIndexOf("-")).replace("-", "");
								
			//58
			for(String source : sourceMap_58.keySet()){
			
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "','58_"+source+"',");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
				
				sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ");			
							
				if(platform.equals("pc"))
				{
					sql.append(" from t_netflow_pc_source_cate_pv_"  + yyyyMM_58);
				}
				else 
				{
					sql.append(" from t_netflow_m_source_cate_pv_"  + yyyyMM_58);
				}
																
				//dateList 里是 本月月初（业务线）、 本月月末（cate）、上月（业务线）、去年当月（cate）
				if(searchDate.equals(dateList.get(0)) || searchDate.equals(dateList.get(2)))
				{
					sql.append(" where business_type = 'ershouchuangxin'  and disp_cate1_name='all'");
				}else
				{
					sql.append(" where ((business_type = 'all' and disp_cate1_name in ('二手市场','二手交易') and disp_cate2_name in ('办公用品/设备','艺术品/收藏品','手机号码','二手设备','通讯业务','成人用品','校园二手','二手求购')) " +
							"or (business_type = 'all' and disp_cate1_name = '宠物' and disp_cate2_name = 'all') " +
							"or (business_type = 'all' and disp_cate1_name in ('家政服务','回收') and disp_cate2_name = '二手回收'))");
				}
				
				if(platform.equals("pc"))
				{
					sql.append(" and source2='all' ");
				}
				
				if(!platform.equals("m")){
					sql.append(" and  disp_cate3_name='all' ");
				}
				
				sql.append(" and "+sourceMap_58.get(source)+ " ");			
				sql.append(" group by stat_date ");

				sql.append(" )a ");	
				sql.append(" :"+ DBType.db_94_data_app.getValue());
										
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
			}
	
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//把总计放入map
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();

		for (NetflowWRData vo : rsList)
		{
			if(vo.getType().contains("all"))
			{
				allCountMap.put(vo.getStatDate()+vo.getType(), vo);
			}
		}
		
		//计算占比
		for (NetflowWRData vo : rsList){
			
			if(vo.getType().contains("all"))
				continue;
		
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
			
		return rsList;
	}
}
